<?php

/*
    Dill is a tool for creating websites with PHP.
    Copyright (C) 2014  Jannik Haberbosch

    This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with this program.  If not, see <http://www.gnu.org/licenses/>.
*/

class SimpleSQLiteQuery
{
	private static $sqlite3_comparison_operators = array( "<>", "<", ">", "=", "IS NOT", "IS" );
	private static $sqlite3_logical_operators = array( "AND", "OR" );
	private static $php_types_to_sqlite3_types = array(
		"integer" => SQLITE3_INTEGER,
		"string" => SQLITE3_TEXT,
		"float" => SQLITE3_FLOAT,
		"NULL" => SQLITE3_NULL
	);

	
	function __construct( $db )
	{
		$this->db = $db;
	}


	public function select( $cols, $tables, $conditions = NULL, $append= NULL )
	{
		// Validate passed parameters.
		if( !$this->check_cols( $cols ) ) throw new Exception( "Errormessage." );
		if( !$this->check_tables( $tables ) ) throw new Exception( "Errormessage." );
		if( ( $conditions != NULL ) && !$this->check_conditions( $conditions ) ) throw new Exception( "Errormessage." );

		// Build the sqlite3 statement and execute it.
		$cols_string = is_array( $cols ) ? implode( ", ", $cols ) : "*";
		$tables_string = implode( ", ", $tables );
		if( $conditions != NULL )
		{
			$conditions_string = "";
			for( $x = 0, $y = 0; $x < count( $conditions ); $x++ )
			{
				if( $x % 2 == 0 )
				{
					$conditions_string .= sprintf( "%s %s :%s ", $conditions[$x][0], $conditions[$x][1], $conditions[$x][0] );
					$y++;
				}
				else
				{
					$conditions_string .= sprintf( "%s ", $conditions[$x][0] );
				}
			}

			$query_string = sprintf( "SELECT %s FROM %s WHERE %s", $cols_string, $tables_string, $conditions_string );
		}
		else
		{
			$query_string = sprintf( "SELECT %s FROM %s", $cols_string, $tables_string );
		}

		if( $append != NULL )
		{
			$query_string = sprintf( "%s %s", $query_string, $append );
		}

		// Prepare the sqlite3 statement.
		if( ( $sqlite3_stmt = $this->db->prepare( $query_string ) ) == FALSE )
		{
			throw new Exception ( "SQLite3 preparation error." );
		}

		// Bind values to the prepared sqlite3 statement if necessary.
		if( $conditions != NULL )
		{
			for( $x = 0; $x < count( $conditions ); $x += 2 )
			{
				$result = $sqlite3_stmt->bindValue( sprintf( ":%s", $conditions[$x][0] ), $conditions[$x][2], self::$php_types_to_sqlite3_types[gettype( $conditions[$x][2] )] );
				if( $result == FALSE ) throw new Exception( "Error" );
			}
		}

		if( ($sqlite3_result = $sqlite3_stmt->execute() ) == FALSE ) throw new Exception( "Error" );
		$rows = array();
		while( ( $row = $sqlite3_result->fetchArray( SQLITE3_ASSOC ) ) )
		{
			$rows[] = $row;
		}
		$sqlite3_result->finalize();
		$sqlite3_stmt->close();
		return $rows;
	}


	public function update( $table, $assignments, $conditions = NULL )
	{
		// Validate passed parameters.
		if( !$this->check_table( $table ) ) throw new Exception( "Error" );
		if( !$this->check_assignments( $assignments ) ) throw new Exception( "Error" );
		if( ( $conditions != NULL ) && !$this->check_conditions( $conditions ) ) throw new Exception( "Error" );

		// Build the sqlite3 statement and execute it.
		$table_string = sprintf( "%s ", $table );

		$assignments_strings = array();
		foreach( $assignments as $assignment )
		{
			$assignments_strings[] = sprintf( "%s = ? ", $assignment[0], $assignment[0] );
		}
		$assignments_string = implode( ", ", $assignments_strings );
		
		if( $conditions != NULL )
		{
			$conditions_string = "";
			for( $x = 0, $y = 0; $x < count( $conditions ); $x++ )
			{
				if( $x % 2 == 0 )
				{
					$conditions_string .= sprintf( "%s %s ? ", $conditions[$x][0], $conditions[$x][1], $conditions[$x][0] );
					$y++;
				}
				else
				{
					$conditions_string .= sprintf( "%s ", $conditions[$x][0] );
				}
			}
			$query_string = sprintf( "UPDATE %s SET %s WHERE %s", $table_string, $assignments_string, $conditions_string );

			// Prepare the sqlite3 statement.
			if( ( $sqlite3_stmt = $this->db->prepare( $query_string ) ) == FALSE ) throw new Exception( "Error" );

			// Bind parameters.
			$counter = 1;
			for( $x = 0; $x < count( $assignments ); $x++ )
			{
				$result = $sqlite3_stmt->bindValue( $counter, $assignments[$x][2], self::$php_types_to_sqlite3_types[gettype( $assignments[$x][2] )] );
				if( $result == FALSE ) throw new Excepetion( "Error" );
				$counter++;
			}
						
			for( $x = 0; $x < count( $conditions ); $x += 2 )
			{
				$result = $sqlite3_stmt->bindValue( $counter, $conditions[$x][2], self::$php_types_to_sqlite3_types[gettype( $conditions[$x][2] )] );
				if( $result == FALSE ) throw new Exception( "Error" );
				$counter++;				
			}
		}
		else
		{
			$query_string = sprintf( "UPDATE %s SET %s", $table_string, $assignments_string );
					
			// Prepare the sqlite3 statement.
			if( ( $sqlite3_stmt = $this->db->prepare( $query_string ) ) == FALSE ) throw new Exception( "Error" );

			// Bind parameters.
			for( $x = 0; $x < count( $assignments ); $x++ )
			{
				$result = $sqlite3_stmt->bindValue( $x, $assignments[$x][2], self::$php_types_to_sqlite3_types[gettype( $assignments[$x][2] )] );
				if( $result == FALSE ) throw new Excepetion( "Error" );				
			}
		}

		// Execute the prepared sqlite3 statement.
		if( $sqlite3_stmt->execute() == FALSE ) throw new Exception ("SQLite3 execution error." );
	}

	
	public function insert( $table, $cols, $values )
	{
		// Validate passed parameters
		if( !$this->check_table( $table ) ) throw new Exception( "Error" );
		if( !$this->check_cols( $cols ) ) throw new Exception( "Error" );
		if( !$this->check_values( $values ) ) throw new Exception( "Error" );

		// Build the sqlite3 statement and execute it.
		$table_string = sprintf( "%s ", $table );
		
		$cols_strings = array();
		$values_strings = array();		
		foreach( $cols as $col )
		{
			$cols_strings[] = sprintf( "%s ", $col );
			$values_strings[] = sprintf( ":%s", $col );			
		}
		$cols_string = implode( ", ", $cols_strings );
		$values_string = implode( ", ", $values_strings );

		$query_string = sprintf( "INSERT INTO %s ( %s ) VALUES ( %s );", $table_string, $cols_string, $values_string );

		// Prepare the sqlite3 statement.
		$sqlite3_stmt = $this->db->prepare( $query_string );
		if( $sqlite3_stmt == FALSE ) throw new Exception( "Error" );

		// Bind parameters to the prepared sqlite3 statement.
		for( $x = 0; $x < count( $values ); $x++ )
		{
			$result = $sqlite3_stmt->bindValue( sprintf( ":%s", $cols[$x] ), $values[$x], self::$php_types_to_sqlite3_types[gettype( $values[$x] )] );
			if( $result == FALSE ) throw new Exception( "Error" );
		}

		// Execute the prepared sqlite3 stmt.
		$sqlite3_result = $sqlite3_stmt->execute();
		if( $sqlite3_result == FALSE ) throw new Exception( "Error" );
	}

	
	public function delete( $table, $conditions = NULL )
	{
		// Validate passed parameters.
		if( !$this->check_table( $table ) ) throw new Exception( "Error" );		
		if( ( $conditions != NULL ) && !$this->check_conditions( $conditions ) ) throw new Exception( "Error" );

		// Build the sqlite statement and execute it.
		$table_string = sprintf( "%s ", $table );
		if( ( $sqlite3_stmt = $this->db->prepare( sprintf( "DELETE FROM %s", $table_string ) ) ) == FALSE ) throw new Exception( "Error" );

		if( $conditions != NULL )
		{
			$conditions_string = "";
			for( $x = 0; $x < count( $conditions ); $x++ )
			{
				if( $x % 2 == 0 )
				{
					$conditions_string .= sprintf( "%s %s :%s ", $conditions[$x][0], $conditions[$x][1], $conditions[$x][0] );
				}
				else
				{
					$conditions_string .= sprintf( "%s ", $conditions[$x][0] );
				}
			}

			// Prepare the sqlite3 statement.
			$sqlite3_stmt = $this->db->prepare( sprintf( "DELETE FROM %s WHERE %s", $table_string, $conditions_string ) );
			if( $sqlite3_stmt == FALSE ) throw new Exception( "Error" );

			// Bind parameters to the prepared sqlite3 statement.
			for( $x = 0; $x < count( $conditions ); $x += 2 )
			{
				$result = $sqlite3_stmt->bindValue( sprintf( ":%s", $conditions[$x][0] ), $conditions[$x][2], self::$php_types_to_sqlite3_types[gettype( $conditions[$x][2] )] );
				if( $result == FALSE ) throw new Excepetion( "Error" );
			}
		}

		// Execute the prepared sqlite3 statement.
		if( $sqlite3_stmt->execute() == FALSE ) throw new Exception( "Error" );
	}


	private function check_values( $values )
	{
		if( !array_reduce( $values, function( $carry, $item ){ return $carry && $this->check_value( $item ); }, TRUE ) ) return FALSE;
		return TRUE;
	}


	private function check_value( $value )
	{
		if( !is_string( $value ) && !is_int( $value ) && !is_float( $value ) && !is_null( $value ) ) return FALSE;
		return TRUE;
	}


	private function check_cols( $cols )
	{
		/* Returns TRUE if $cols is a string '*' or an array, where all elements
		are of type string.  Otherwise FALSE is returned.

		Parameters 		$cols
						mixed
						$cols can either be an array, containing the names of
						all columns, or a string with an asterisk '*' which stands
						for all columns.

		Return values	TRUE/FALSE
						boolean
						Please see the description for more information.

		*/
		if( is_string( $cols ) && strcmp( $cols, "*" ) == 0 ) return TRUE;
		if( !is_array( $cols ) ) return FALSE;
		if( !array_reduce( $cols, function( $carry, $item ){ return $carry && is_string( $item ); }, TRUE ) ) return FALSE;

		return TRUE;
	}


	private function check_table( $table )
	{
		if( !is_string( $table ) ) return FALSE;
		return TRUE;
	}

	
	private function check_tables( $tables )
	{
		/* $tables has got to be an array which consists of one or more strings.
		If not, FALSE is returned.  Otherwise TRUE is returned.

		Parameters		$tables
						array
						An array that contains the names of the sqlite tables.

		Return values	TRUE/FALSE
						boolean
						Take a look at the description of this function for more
						information.
		
		*/
		if( !is_array( $tables ) ) return FALSE;
		if( !array_reduce( $tables, function( $carry, $item ){ return $carry && is_string( $item ); }, TRUE ) ) return FALSE;

		return TRUE;
	}

	
	private function check_conditions( $conditions )
	{
		/*

		*/
		
		if( !is_array( $conditions ) ) return FALSE;
		if( count( $conditions ) == 0 ) return FALSE;
		// More than one conditions need to be connected with a logical operator.
		if( count( $conditions > 1 ) && ( count( $conditions ) % 2 == 0 ) ) return FALSE;
		if( count( $conditions ) == 1 )
		{
			return $this->check_single_condition( $conditions[0] );
		}
		else
		{
			return $this->check_multiple_conditions( $conditions );
		}
	}


	private function check_single_condition( $condition )
	{
		if( !is_array( $condition ) ) return FALSE;
		if( count( $condition ) != 3 ) return FALSE;
		if( !is_string( $condition[0] ) ) return FALSE;
		if( !in_array( $condition[1], self::$sqlite3_comparison_operators ) ) return FALSE;
		if( !is_string( $condition[2] ) && !is_int( $condition[2] ) && !is_float( $condition[2] ) && !is_null( $condition[2] ) ) return FALSE;

		return TRUE;
	}


	private function check_multiple_conditions( $conditions )
	{
		/* More than one condition present.  Additional logical operators
		are required. */
		// Every uneven element is a logical operator:
		$all_conditions = array();
		$logical_operators = array();
		foreach( $conditions as $key => $value )
		{
			if( $key % 2 == 0 )
			{
				$all_conditions[] = $value;
			}
			else
			{
				$logical_operators[] = $value;
			}
		}

		// Make sure all conditions are valid.
		if( !array_reduce( $all_conditions, function( $carry, $item ){ return $carry && $this->check_single_condition( $item ); }, TRUE ) ) return FALSE;
		
		// Make sure all logical operators are valid.
		if( !array_reduce( $logical_operators, function( $value ){ return in_array( $value, self::$sqlite3_logical_operators ); }, TRUE ) ) return FALSE;	

		return TRUE;
	}

	
	private function check_assignments( $assignments )
	{
		/*
		
		*/
		if( !is_array( $assignments ) ) return FALSE;
		if( count( $assignments ) == 0 ) return FALSE;
		if( !array_reduce( $assignments, function( $carry, $item ){ return $carry && $this->check_single_assignment( $item ); }, TRUE ) ) return FALSE;
		
		return TRUE;
		
	}


	private function check_single_assignment( $assignment )
	{
		/*
		
		*/
		if( !is_array( $assignment ) ) return FALSE;
		if( count( $assignment ) != 3 ) return FALSE;
		if( !is_string( $assignment[0] ) ) return FALSE;
		if( !is_string( $assignment[1] ) || strcmp( $assignment[1], "=" ) != 0 ) return FALSE;
		if( !is_string( $assignment[2] ) && !is_int( $assignment[2] ) && !is_float( $assignment[2] ) && !is_null( $assignment[2] ) ) return FALSE;

		return TRUE;
	}


	public function get_insertid()
	{
		return $this->db->lastInsertRowID();
	}

	
}
/*
$db = new SQLite3( "db.sqlite" );
$ssq = new SimpleSQLiteQuery( $db );

// Testing delete.
/*
try
{
	$ssq->delete(
		"page",
		array(
			array( "sort_id", "=", 12314 ),
			array( "OR" ),
			array( "name", "=", "MASTER47" )
		)
	);
}
catch( Exception $e )
{
	echo $e->getMessage();
}
/*
// Testing insert.
try
{
	$ssq->insert(
		"page",
		array(
			"template",
			"name",
			"parent_id",
			"sort_id"
		),
		array(
			"mytemplate.html",
			"MASTER47",
			10,
			12314
		)
	);
}
catch( Excepetion $e )
{
	echo $e->getMessage();
	die();
}

/*
// Testing update.
try
{
	$ssq->update(
		"page",
		array(
			array( "template", "=", "jannik.html" ),
			array( "id", "=", 99 )
		),
		array(
			array( "sort_id", "=", 3 ),
			array( "AND" ),
			array( "parent_id", "=", 7 )
		)
	);
}
catch( Exception $e )
{
	echo $e->getMessage();
	die();
}

*/
// Testing select.
/*
try
{
	$rows = $ssq->select(
		"*",
		array( "page" )
	);
}
catch( Exception $e )
{
	echo $e->getMessage();
	die();
}
print_r( $rows );
*/
?>
